#!/bin/bash

# 定义变量方便修改
table_db=cy_database
hive=/home/software/hive-2.3.6/bin/hive

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$1" ] ;then
    do_date=$1
else 
    do_date=`date -d "-1 day" +%F`  
fi 

sql="
set hive.exec.dynamic.partition.mode=nonstrict;

with
tmp_college_graduate as
(
    select 
        id,name ,
		sex,
		birthday,
		cast(round(DATEDIFF(current_date(),cast(birthday as date))/365) as string) AS age,
        nation,
		idcard,
		census_register_area census_register_area_code ,
		census_type ,
        area_code ,
		phone,
		contact_qq,
		contact_wei,
		education,
		school ,
		edu_time ,
        graduation,
		census_register,
		home_address,
		cast(round( months_between(current_date(),cast(edu_time as date))/12) as string) as work_year,
        startup_code,
		personal_state,
		work_whereabouts,
		company_work,
		autonomy_work,
        special_position_work,
		unemploy_type,
		now_work_company ,
		cast(date_format(update_time,'yyyy-MM-dd') as string) update_time
    from cy_database.dwd_college_graduate cg
    where date_format(cg.dt,'yyyy-MM-dd')='2020-06-17'
),

tmp_unemploy_person as(
    select 
         id,
        name ,
        cast(sex as string) sex,
        cast(birthday as string) birthday,
        cast(round(DATEDIFF(current_date(),cast(birthday as date))/365) as string) AS age,
        null nation,
        idcard ,
        null census_register_area_code,
        null census_type,
        cast( now_address as string) as area_code,
        phone,
        null contact_qq ,
        null contact_wei ,
        education ,
        school ,
        null edu_time,
        graduation ,
        qcellcore   census_register,
        census_register home_address,
        cast(round(rand()*4) as string)  work_year,
        startup_code,
        cast('0' as string) personal_state,
        null work_whereabouts,
        null company_work,
        null autonomy_work,
        null special_position_work,
        null unemploy_type,
        null now_work_company,
       cast(current_date() as string)  update_time
    from cy_database.dwd_unemploy_person up
    where date_format(up.dt,'yyyy-MM-dd')='2020-06-17'
),


tmp_person_apply_job as(
        select
            id ,
            name,
            sex ,
            concat_ws('-',substring(idcard,7,4) , substring(idcard,11,2),substring(idcard,13,2)) birthday,
            cast(round(DATEDIFF(current_date(),cast(birthday as date))/365) as string) AS age,
            null nation,
            idcard,
            registered_province_code as census_register_area_code,
            null census_type,
            now_area_code as area_code,
            phone,
            null contact_qq,
            null contact_wei,
            education,
            null school,
            null edu_time,
            null graduation,
            registered_city_code as census_register,
            now_city_code home_address,
            cast(work_year as string) work_year,
            null startup_code,
            cast('0' as string) personal_state,
            null work_whereabouts,
            null company_work,
            null autonomy_work,
            null special_position_work,
            unemployment_situation as unemploy_type,
            null now_work_company,
            cast(current_date() as string) update_time
        from cy_database.dwd_person_apply_job paj
        where date_format(paj.dt,'yyyy-MM-dd')='2020-06-17'

),


tmp_probation_people_info as(
    select
        id ,
        name,
        sex ,
        birthday,
        age,
        nation,
        idcard,
        census_register_area_code,
        census_type,
        area_code,
        phone,
        contact_qq,
        contact_wei,
        education,
        school,
        edu_time,
        graduation,
        census_register,
        home_address,
        work_year,
        startup_code,
        personal_state,
         work_whereabouts,
        company_work,
        autonomy_work,
        special_position_work,
        unemploy_type,
        now_work_company,
        cast(update_time as string) update_time
    from (
            select 
                id ,
                name,
                sex ,
                birthday,
                age,
                nation,
                idcard,
                census_register_area_code,
                census_type,
                area_code,
                phone,
                contact_qq,
                contact_wei,
                education,
                school,
                edu_time,
                 graduation,
                census_register,
                home_address,
                work_year,
                startup_code,
                personal_state,
                 work_whereabouts,
                company_work,
                autonomy_work,
                special_position_work,
                unemploy_type,
                now_work_company,
                update_time,
                row_number() over(partition by name order by id) rn
            from (
                    select
                        id,
                        name,
                        null sex,
                        concat_ws('-',substring(idcard,7,4) , substring(idcard,11,2),substring(idcard,13,2))  birthday,
                        cast(months_between(current_date(),cast(concat_ws('-',substring(idcard,7,4), substring(idcard,11,2), substring(idcard,13,2)) as date))/12 as string) age,
                        nation,
                        idcard,
                        null census_register_area_code,
                        null census_type,
                        null area_code,
                        phone,
                        null contact_qq,
                        null contact_wei,
                        education ,
                        school,
                        edu_time ,
                        graduation,
                        null census_register,
                        home_address ,
                        cast('0' as string) work_year,
                        null startup_code,
                        cast('1'as string) as personal_state,
                        null work_whereabouts,
                        null company_work,
                        null autonomy_work,
                        null special_position_work,
                        null unemploy_type,
                        ent_name now_work_company,
                        cast(update_time as string)update_time
                    from cy_database.dwd_probation_people_info ppi
                    where date_format(ppi.dt,'yyyy-MM-dd')='2020-06-17'
                )tmp_a
        )tmp_b
    where rn =1

    
)

insert overwrite table cy_database.dws_person_detail partition(dt='2020-06-17')
select
		id,
		name,
		sex,
		birthday,
		age,
		nation,
		idcard,
		census_register_area_code,
		census_type,
		area_code,
		phone,
		contact_qq,
		contact_wei,
		education,
		school,
		edu_time,
		graduation,
		census_register,
		home_address,
		work_year,
		startup_code,
		personal_state,
		work_whereabouts,
		company_work,
		autonomy_work,
		special_position_work,
		unemploy_type,
		now_work_company,
		update_time
from (
	select
		id,
		name,
		sex,
		birthday,
		age,
		nation,
		idcard,
		census_register_area_code,
		census_type,
		area_code,
		phone,
		contact_qq,
		contact_wei,
		education,
		school,
		edu_time,
		graduation,
		census_register,
		home_address,
		work_year,
		startup_code,
		personal_state,
		work_whereabouts,
		company_work,
		autonomy_work,
		special_position_work,
		unemploy_type,
		now_work_company,
		update_time,
		row_number() over(partition by name) rn
	from (
			select
				id,
				name,
				sex,
				birthday,
				age,
				nation,
				idcard,
				census_register_area_code,
				census_type,
				area_code,
				phone,
				contact_qq,
				contact_wei,
				education,
				school,
				edu_time,
				graduation,
				census_register,
				home_address,
				work_year,
				startup_code,
				personal_state,
				work_whereabouts,
				company_work,
				autonomy_work,
				special_position_work,
				unemploy_type,
				now_work_company,
				update_time
			from tmp_college_graduate
			union
			select
				id,
				name,
				sex,
				birthday,
				age,
				nation,
				idcard,
				census_register_area_code,
				census_type,
				area_code,
				phone,
				contact_qq,
				contact_wei,
				education,
				school,
				edu_time,
				graduation,
				census_register,
				home_address,
				work_year,
				startup_code,
				personal_state,
				work_whereabouts,
				company_work,
				autonomy_work,
				special_position_work,
				unemploy_type,
				now_work_company,
				update_time
			from tmp_unemploy_person
			union
			select
				id,
				name,
				sex,
				birthday,
				age,
				nation,
				idcard,
				census_register_area_code,
				census_type,
				area_code,
				phone,
				contact_qq,
				contact_wei,
				education,
				school,
				edu_time,
				graduation,
				census_register,
				home_address,
				work_year,
				startup_code,
				personal_state,
				work_whereabouts,
				company_work,
				autonomy_work,
				special_position_work,
				unemploy_type,
				now_work_company,
				update_time
			from tmp_person_apply_job
			union
			select
				id,
				name,
				sex,
				birthday,
				age,
				nation,
				idcard,
				census_register_area_code,
				census_type,
				area_code,
				phone,
				contact_qq,
				contact_wei,
				education,
				school,
				edu_time,
				graduation,
				census_register,
				home_address,
				work_year,
				startup_code,
				personal_state,
				work_whereabouts,
				company_work,
				autonomy_work,
				special_position_work,
				unemploy_type,
				now_work_company,
				update_time	
			from tmp_probation_people_info	
		)aaa

	) aaaa
where rn = 1;
	

with
tmp_unemploy_person as
(
	select
		id,
		name ,
		sex ,
		birthday,
		cast(round(months_between(current_date(),birthday)/12) as string) as age,
		null nation,
		idcard,
		null census_register_area_code,
		null census_type,
		now_address area_code,
		phone,
		null contact_qq,
		null contact_wei,
		education,
		school,
		edu_time,
		graduation ,
		qcellcore census_register,
		null home_address,
		cast(round( months_between(current_date(),edu_time)/12) as string) as work_year,
		null startup_code,
		cast('0' as string) personal_state,
		now_address,
		unemploy_register_time ,
		unemploy_cause ,
		unemploy_time,
		old_company ,
		sign ,
		update_time,
		card_photo_back ,
		card_photo_obverse,
		hard_special_certificate_1 ,
		hard_special_certificate_2,
		hard_special_certificate_3 ,
		examine_status ,
		hard_situation ,
		is_allowance_family,
		cast(current_date() as string) undate_time
	from cy_database.dwd_unemploy_person up
	where date_format(up.dt,'yyyy-MM-dd')='2020-06-17'
),

tmp_person_apply_job as
(
	select
		id,
		name,
		sex,
		birthday,
		cast (round(months_between(current_date(),concat_ws('-',substring(idcard,7,4) , substring(idcard,11,2),substring(idcard,13,2)))/12) as string)  as age,
		null nation,
		idcard,
		registered_city_code as census_register_area_code,
		null census_type,
		null area_code,
		phone,
		null contact_qq ,
		null contact_wei,
		education ,
		null school,
		null edu_time,
		null graduation,
		registered_city_code as census_register ,
		null home_address,
		work_year,
		null startup_code ,
		now_work_situation personal_state,
		now_city_code as now_address,
		update_time unemploy_register_time,
		null unemploy_cause,
		update_time unemploy_time	,
		null old_company,
		null  sign,
		update_time,
		null card_photo_back,
		null card_photo_obverse ,
		null hard_special_certificate_1,
		null hard_special_certificate_2,
		null hard_special_certificate_3 ,
		null examine_status,
		null hard_situation ,
		null is_allowance_family,
		update_time undate_time
	from cy_database.dwd_person_apply_job paj
	where personal_state = 0 and date_format(paj.dt,'yyyy-MM-dd')='2020-06-17'
)

insert overwrite table cy_database.dws_person_unemploy_detail partition(dt ='2020-06-17')
		select
			id,
			name ,
			sex ,
			birthday,
			age,
			nation,
			idcard,
			census_register_area_code,
			census_type,
			area_code,
			phone,
			contact_qq,
			contact_wei,
			education,
			school,
			edu_time,
			graduation ,
			census_register,
			home_address,
			work_year,
			startup_code,
			personal_state,
			now_address,
			unemploy_register_time ,
			unemploy_cause ,
			unemploy_time,
			old_company ,
			sign ,
			update_time,
			card_photo_back ,
			card_photo_obverse,
			hard_special_certificate_1 ,
			hard_special_certificate_2,
			hard_special_certificate_3 ,
			examine_status ,
			hard_situation ,
			is_allowance_family,
			undate_time
		from tmp_unemploy_person
		union all
		select
			id,
			name ,
			sex ,
			birthday,
			age,
			nation,
			idcard,
			census_register_area_code,
			census_type,
			area_code,
			phone,
			contact_qq,
			contact_wei,
			education,
			school,
			edu_time,
			graduation ,
			census_register,
			home_address,
			work_year,
			startup_code,
			personal_state,
			now_address,
			unemploy_register_time ,
			unemploy_cause ,
			unemploy_time,
			old_company ,
			sign ,
			update_time,
			card_photo_back ,
			card_photo_obverse,
			hard_special_certificate_1 ,
			hard_special_certificate_2,
			hard_special_certificate_3 ,
			examine_status ,
			hard_situation ,
			is_allowance_family,
			undate_time
		from tmp_person_apply_job
;

with
tmp_probation_people_info as (
	select
		id,
		name,
		null sex,
		birthday,
		cast(round(months_between('2020-06-17', birthday)/12) as string) as age,
		nation,
		idcard,
		null census_register_area_code,
		null census_type,
		null area_code,
		phone,
		null contact_qq,
		null contact_wei,
		education,
		school,
		edu_time,
		graduation,
		null census_register,
		home_address,
		null startup_code,
		case when ent_name is not null then '1' else null end as personal_state_code,
		null ent_id ,
		ent_name,
		industry_code,
		industry_type,
		business_code,
		business_type,
		null position_code,
		null position,
		probation_start_time entry_work,
		null out_work,
		cast(round(months_between(current_date(),edu_time)/12) as string) work_year
	from cy_database.dwd_probation_people_info ppi
	where ppi.dt='2020-06-17' and ppi.id is not null

),


tmp_enterprise_person_info as(
	select
		id,
		name,
		sex,
		birthday,
		cast(round(months_between(current_date() , birthday)/12 ) as string) age,
		null nation,
		null idcard,
		native_code census_register_area_code,
		null census_type,
		area_code,
		phone,
		null contact_qq ,
		null contact_wei ,
		education,
		school,
		null edu_time,
		null graduation,
		native_place census_register,
		home_address,
		null startup_code,
		cast('1' as string ) personal_state_code,
		ent_id,
		ent_name,
		industry_code ,
		industry_type,
		business_code ,
		business_type ,
		position_code,
		position,
		entry_work ,
		out_work,
		work_year
	from cy_database.dwd_enterprise_person_info epi
	where epi.dt='2020-06-17' and epi.id is not null


)

insert overwrite table cy_database.dws_person_work_detail partition(dt='2020-06-17')
	select
		id,
		name,
		sex,
		birthday,
		age,
		nation,
		idcard,
		census_register_area_code,
		census_type,
		area_code,
		phone,
		contact_qq,
		contact_wei,
		education,
		school,
		edu_time,
		graduation,
		census_register,
		home_address,
		startup_code,
		personal_state_code,
		ent_id,
		ent_name,
		industry_code,
		industry_type,
		business_code,
		business_type,
		position_code,
		position,
		entry_work,
		out_work,
		work_year
	from tmp_probation_people_info

    union
	select
		id,
		name,
		sex,
		birthday,
		age,
		nation,
		idcard,
		census_register_area_code,
		census_type,
		area_code,
		phone,
		contact_qq,
		contact_wei,
		education,
		school,
		edu_time,
		graduation,
		census_register,
		home_address,
		startup_code,
		personal_state_code,
		ent_id,
		ent_name,
		industry_code,
		industry_type,
		business_code,
		business_type,
		position_code,
		position,
		entry_work,
		out_work,
		work_year
	from tmp_enterprise_person_info
;


with
tmp_enterprise as
(	select
		id,
		user_id,
		ent_name,
		short_name,
		social_security_number,
		industry_code,
		business_code,
		business_nature_code,
		industry_type,
		business_type,
		business_nature,
		uniform_social_credit_code,
		scale,
		scale_code,
		financing_stage,
		registered_capital,
		registration_authority,
		establishment,
		introduction,
		logo,
		corporate_welfare,
		business_license,
		organization_code_certificate,
		id_card_front,
		id_card_back,
		province_code,
		city_code,
		area_code,
		street_code,
		province,
		city,
		area,
		street,
		address,
		latitude,
		longitude,
		status,
		views,
		contact_person,
		phone,
		manager_name,
		manager_phone,
		manager_id_card_num,
		manager_qq,
		manager_email,
		manager_position,
		created_time,
		updated_time,
		examine_status,
		register_address,
		sys_area_code,
		batchNumber,
		register_province_code,
		register_city_code,
		register_area_code,
		register_province,
		register_city,
		register_area,
		ent_level,
		is_important
  from cy_database.dwd_enterprise ee
  where ee.dt='2020-06-17' and ee.id is not null
)

insert overwrite table cy_database.dws_enterprise_info partition(dt='2020-06-17')
	select
		id,
		user_id,
		ent_name,
		short_name,
		social_security_number,
		industry_code,
		business_code,
		business_nature_code,
		industry_type,
		business_type,
		business_nature,
		uniform_social_credit_code,
		scale,
		scale_code,
		financing_stage,
		registered_capital,
		registration_authority,
		establishment,
		introduction,
		logo,
		corporate_welfare,
		business_license,
		organization_code_certificate,
		id_card_front,
		id_card_back,
		province_code,
		city_code,
		area_code,
		street_code,
		province,
		city,
		area,
		street,
		address,
		latitude,
		longitude,
		status,
		views,
		contact_person,
		phone,
		manager_name,
		manager_phone,
		manager_id_card_num,
		manager_qq,
		manager_email,
		manager_position,
		created_time,
		updated_time,
		examine_status,
		register_address,
		sys_area_code,
		batchNumber,
		register_province_code,
		register_city_code,
		register_area_code,
		register_province,
		register_city,
		register_area,
		ent_level,
		is_important
	from tmp_enterprise
;


with
tmp_enterprise_business as
(
	select
		eb.id,
		eb.ent_id,
		eb.ent_name,
		ee.short_name,
		ee.social_security_number,
		ee.industry_code,
		ee.business_code,
		ee.business_nature_code,
		ee.industry_type,
		ee.business_type,
		ee.business_nature,
		ee.uniform_social_credit_code,
		ee.scale,
		ee.scale_code,
		ee.financing_stage,
		ee.registered_capital,
		ee.registration_authority,
		ee.establishment,
		ee.introduction,
		eb.turnover,
		eb.turnover_profit,
		eb.market_value,
		eb.risk_info,
		eb.warning_info,
		eb.registered_capital_update,
		eb.alter_time

	from cy_database.dwd_enterprise_business eb
	join cy_database.dwd_enterprise ee
	on eb.ent_name= ee.ent_name
	where  eb.dt='2020-06-17' and eb.id is not null and ee.dt='2020-06-17' and ee.id is not null

)
insert overwrite table cy_database.dws_enterprise_business_detail partition(dt='2020-06-17')
	select
		id,
		ent_id,
		ent_name,
		short_name,
		social_security_number,
		industry_code,
		business_code,
		business_nature_code,
		industry_type,
		business_type,
		business_nature,
		uniform_social_credit_code,
		scale,
		scale_code,
		financing_stage,
		registered_capital,
		registration_authority,
		establishment,
		introduction,
		turnover,
		turnover_profit,
		market_value,
		risk_info,
		warning_info,
		registered_capital_update,
		alter_time
	from tmp_enterprise_business

;


with
tmp_enterprise_position_require_detail as(
	select
		id,
		ent_id,
		ent_name,
		industry_code,
		business_code,
		business_nature_code,
		industry_type,
		business_type,
		business_nature,
		posts_name,
		function_type,
		posts,
		sex,
		experience,
		education,
		salary,
		age_range,
		job_nature,
		description,
		keyword,
		keyword_two,
		keyword_three,
		province_code,
		city_code,
		area_code,
		street_code,
		province,
		city,
		area,
		street,
		address,
		latitude,
		longitude,
		status,
		views,
		publish,
		skill,
		sys_area_code,
		batchNumber,
		age_begin,
		age_end,
		salary_begin,
		salary_end,
		source,
		update_time
	from cy_database.dwd_enterprise_position_require_detail eprd
	where eprd.dt='2020-06-17' and eprd.id is not null

)
insert overwrite table cy_database.dws_enterprise_position_require_detail partition(dt='2020-06-17')
	select
		id,
		ent_id,
		ent_name,
		industry_code,
		business_code,
		business_nature_code,
		industry_type,
		business_type,
		business_nature,
		posts_name,
		function_type,
		posts,
		sex,
		experience,
		education,
		salary,
		age_range,
		job_nature,
		description,
		keyword,
		keyword_two,
		keyword_three,
		province_code,
		city_code,
		area_code,
		street_code,
		province,
		city,
		area,
		street,
		address,
		latitude,
		longitude,
		status,
		views,
		publish,
		skill,
		sys_area_code,
		batchNumber,
		age_begin,
		age_end,
		salary_begin,
		salary_end,
		source,
		update_time
	from tmp_enterprise_position_require_detail

;


with
tmp_enterprise_person_info as (
	select
		id,
		identification,
		name,
		province_code,
		city_code,
		area_code,
		province,
		city,
		area,
		native_code,
		native_place,
		home_address,
		politics_code,
		politics,
		sex,
	    birthday,
		marital_status,
		education,
		school,
		ent_id,
		ent_name,
		industry_code,
		business_code,
		position_code,
		industry_type,
		business_type,
		position,
		phone,
		entry_work,
		out_work,
		work_year
	from cy_database.dwd_enterprise_person_info epi
	where epi.dt='2020-06-17' and epi.id is not null



)

insert overwrite table cy_database.dws_enterprise_person_info partition(dt='2020-06-17')
	select
		id,
		identification,
		name,
		province_code,
		city_code,
		area_code,
		province,
		city,
		area,
		native_code,
		native_place,
		home_address,
		politics_code,
		politics,
		sex,
		birthday,
		marital_status,
		education,
		school,
		ent_id,
		ent_name,
		industry_code,
		business_code,
		position_code,
		industry_type,
		business_type,
		position,
		phone,
		entry_work,
		out_work,
		work_year
	from tmp_enterprise_person_info
;




with
tmp_enterprise_probation_years as (
	select
		id,
		ent_id,
		ent_name,
		business_nature_code ,
		business_nature,
		plan_probation_total ,
		actual_probation_total ,
		probation_retain ,
		already_provide_alimony,
		already_probation_subsidy,
		probation,
		credit_code ,
		updated_time
	from cy_database.dwd_enterprise_probation_years epy
	where epy.dt='2020-06-17' and epy.id is not null

)

insert overwrite table cy_database.dws_enterprise_probation_years partition(dt='2020-06-17')
	select
		id,
		ent_id,
		ent_name,
		business_nature_code ,
		business_nature,
		plan_probation_total ,
		actual_probation_total ,
		probation_retain ,
		already_provide_alimony,
		already_probation_subsidy,
		probation,
		credit_code ,
		updated_time
	from tmp_enterprise_probation_years
;


with
tmp_out_work  as(
	select
		id,
		name,
		phone,
		birthday,
		idcard,
		sex,
		census_province_code,
		census_city_code,
		census_area_code,
		census_town_code,
		census_village_code,
		census_province,
		census_city,
		census_area,
		census_town,
		census_village,
		skill_specialty,
		skill_level,
		is_work,
		work_status,
		work_town_code,
		work_town,
		work_company,
		work_position,
		work_salary,
		out_work_address,
		out_work_company,
		out_work_position,
		out_date,
		is_back,
		out_salary ,
		ni_back_company,
		ni_positon,
		expect_salary,
		remark,
		is_settle,
		education ,
		school,
		entrance_time,
		edu_time,
		graduation ,
		title_name,
		title_time,
		update_time
	from cy_database.dwd_out_work ow
	where ow.dt='2020-06-17' and ow.id is not null

)

insert overwrite table cy_database.dws_out_work  partition(dt='2020-06-17')
	select
		id,
		name,
		phone,
		birthday,
		idcard,
		sex,
		census_province_code,
		census_city_code,
		census_area_code,
		census_town_code,
		census_village_code,
		census_province,
		census_city,
		census_area,
		census_town,
		census_village,
		skill_specialty,
		skill_level,
		is_work,
		work_status,
		work_town_code,
		work_town,
		work_company,
		work_position,
		work_salary,
		out_work_address,
		out_work_company,
		out_work_position,
		out_date,
		is_back,
		out_salary,
		ni_back_company,
		ni_positon,
		expect_salary,
		remark,
		is_settle,
		education,
		school,
		entrance_time,
		edu_time,
		graduation,
		title_name,
		title_time,
		update_time
	from tmp_out_work

;


with
tmp_college_graduate  as(
	select
		id,
		name,
		sex,
		birthday,
		nation,
		idcard,
		census_register_area as census_register_area_code,
		census_type,
		census_register_date,
		area_code,
		phone,
		education,
		school,
		edu_time,
		graduation,
		home_address,
		enterprise_recruitment,
		startup_code,
		personal_state,
		work_whereabouts,
		company_work,
		autonomy_work,
		special_position_work,
		unemploy_type,
		now_work_company,
		work_other,
		keep_study,
		register_time,
		contact_qq,
		contact_wei,
		semester_census_address,
		qcellcore,
		hard_situation,
		other_hard_situation,
		archives_code,
		remark,
		update_time,
		census_register,
		colleges_type,
		archives_unit,
		is_internship_activity,
		work_collective_type,
		is_local,
		is_chuangye,
		card_photo_obverse,
		card_photo_back,
		education_photo,
		report_photo,
		train_type,
		now_industry_code,
		working_area

	from cy_database.dwd_college_graduate cg
	where cg.dt='2020-06-17' and cg.id is not null

)

insert overwrite table cy_database.dws_college_graduate_output  partition(dt='2020-06-17')
	select
		id,
		name,
		sex,
		birthday,
		nation,
		idcard,
		census_register_area_code,
		census_type,
		census_register_date,
		area_code,
		phone,
		education,
		school,
		edu_time,
		graduation,
		home_address,
		enterprise_recruitment,
		startup_code,
		personal_state,
		work_whereabouts,
		company_work,
		autonomy_work,
		special_position_work,
		unemploy_type,
		now_work_company,
		work_other,
		keep_study,
		register_time,
		contact_qq,
		contact_wei,
		semester_census_address,
		qcellcore,
		hard_situation,
		other_hard_situation,
		archives_code,
		remark,
		update_time,
		census_register,
		colleges_type,
		archives_unit,
		is_internship_activity,
		work_collective_type,
		is_local,
		is_chuangye,
		card_photo_obverse,
		card_photo_back,
		education_photo,
		report_photo,
		train_type,
		now_industry_code,
		working_area
	from tmp_college_graduate
;


with
tmp_job_fair  as(
	select \
		id,
		hold_date,
		hold_address,
		week_day,
		job_fair_type,
		key_service_type,
		enterprise_sum,
		position_sum,
		admission_sum,
		reach_work_sum,
		sign_ldht_sum,
		work_person,
		job_fair_name
	from cy_database.dwd_job_fair jf
	where jf.dt='2020-06-17' and jf.id is not null


)

insert overwrite table cy_database.dws_job_fair  partition(dt='2020-06-17')
	select
		id,
		hold_date,
		hold_address,
		week_day,
		job_fair_type,
		key_service_type,
		enterprise_sum,
		position_sum,
		admission_sum,
		reach_work_sum,
		sign_ldht_sum,
		work_person,
		job_fair_name
	from tmp_job_fair
;



;


"

$hive -e "$sql"